import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_境内升学专业相关度分布(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None,
    education: Optional[str] = None
) -> Dict[str, Any]:
    """
    境内升学专业相关度分布 - 指标计算函数
    
    ## 指标说明
    该指标用于计算升学专业与升学前专业的相关程度分布情况，包括：
    1. 各选项的详细分布（很相关、相关、基本相关、不相关、很不相关）
    2. 前三项（相关度）的汇总比例
    3. 每个选项的人数、占比和百分比
    
    基于问题编码 T00000644，计算前三项占前五项的比值，并返回完整的分布明细。
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        education (Optional[str]): 学历筛选条件，可选值：本科毕业生/专科毕业生/硕士研究生/博士研究生
        
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159],
        "education": "本科毕业生"
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok", 
        "code": 0,
        "result": {
            "summary": {
                "related_ratio": 0.8741,
                "total_count": 143,
                "related_count": 125,
                "description": "升学专业与原专业相关度为 87.41%"
            },
            "distribution": [
                {
                    "option": "很相关",
                    "count": 58,
                    "ratio": 0.4056,
                    "percentage": "40.56%"
                },
                {
                    "option": "相关",
                    "count": 38,
                    "ratio": 0.2657,
                    "percentage": "26.57%"
                },
                {
                    "option": "基本相关",
                    "count": 29,
                    "ratio": 0.2028,
                    "percentage": "20.28%"
                },
                {
                    "option": "不相关",
                    "count": 13,
                    "ratio": 0.0909,
                    "percentage": "9.09%"
                },
                {
                    "option": "很不相关",
                    "count": 5,
                    "ratio": 0.0350,
                    "percentage": "3.50%"
                }
            ]
        }
    }
    ```
    """
    logger.info(f"开始计算指标: 境内升学专业相关度分布, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year, dy_target_items, split_tb_paper 
        FROM client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        split_tb_paper = project_info['split_tb_paper']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}, split_tb_paper={split_tb_paper}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 查询问卷信息
        questionnaire_sql = f"""
        SELECT id, dy_target 
        FROM wt_template_customer 
        WHERE id IN ({','.join(['%s'] * len(questionnaire_ids))})
        """
        questionnaires = db.fetchall(questionnaire_sql, tuple(questionnaire_ids))
        if not questionnaires:
            raise ValueError(f"未找到问卷ID集合={questionnaire_ids}的配置信息")
        
        logger.info(f"查询到问卷信息: {questionnaires}")

        # 4. 过滤特定调研对象的问卷
        valid_questionnaire_ids = [q['id'] for q in questionnaires if q['dy_target'] == 'GRADUATE_SHORT']
        if not valid_questionnaire_ids:
            raise ValueError("未找到目标调研对象(GRADUATE_SHORT)的问卷ID")
            
        logger.info(f"找到有效问卷ID: {valid_questionnaire_ids}")

        # 5. 查询问题信息
        question_sql = """
        SELECT id, wt_code, wt_obj 
        FROM wt_template_question_customer 
        WHERE cd_template_id = %s AND wt_code = 'T00000644' AND is_del = 0
        """
        question_info = db.fetchone(question_sql, (valid_questionnaire_ids[0],))
        if not question_info:
            raise ValueError("未找到问题编码 T00000644 的问题信息")
            
        logger.info(f"找到问题信息: {question_info['id']}")

        # 6. 解析问题选项
        wt_obj = json.loads(question_info['wt_obj'])
        options = []
        for item in wt_obj['itemList']:
            options.append({
                'key': item['key'],
                'val': item['val'],
                'weight': item.get('weight', 1)
            })

        # 7. 构建动态表名
        answer_table = f"re_dy_paper_answer_{split_tb_paper}"
        student_table = f"dim_client_target_baseinfo_student_{item_year}"

        # 8. 处理学历参数（空字符串转为None）
        if education == '':
            education = None
        
        # 9. 构建SQL查询条件
        education_condition = ""
        if education:
            if education not in ["本科毕业生", "专科毕业生", "硕士研究生", "博士研究生"]:
                raise ValueError(f"学历参数错误，必须是: 本科毕业生、专科毕业生、硕士研究生、博士研究生")
            education_condition = f"AND s.education = '{education}'"
        
        logger.info(f"学历筛选: {education if education else '不限制'}")

        # 10. 执行SQL查询
        sql = f"""
        SELECT
            SUM(t1.c1) as c1,
            SUM(t1.c2) as c2,
            SUM(t1.c3) as c3,
            SUM(t1.c4) as c4,
            SUM(t1.c5) as c5
        FROM {answer_table} t1
        JOIN {student_table} s ON t1.target_no = s.target_no
        WHERE
            t1.cd_template_id = %s
            AND t1.wid = %s
            AND t1.ans_true = 1
            AND s.shard_tb_key = %s
            AND s.item_year = %s
            {education_condition}
        """
        params = (valid_questionnaire_ids[0], question_info['id'], shard_tb_key, item_year)
        result = db.fetchone(sql, params)
        
        if not result:
            raise ValueError("未找到有效的答案数据")
        
        # 11. 计算各选项的数量和占比
        counts = {
            'c1': result['c1'] or 0,  # 很相关
            'c2': result['c2'] or 0,  # 相关
            'c3': result['c3'] or 0,  # 基本相关
            'c4': result['c4'] or 0,  # 不相关
            'c5': result['c5'] or 0   # 很不相关
        }
        
        total_count = sum(counts.values())
        logger.info(f"各选项统计: {counts}, 总计: {total_count}")
        
        if total_count == 0:
            logger.warning("总答题人数为0，返回空结果")
            return {
                "success": True,
                "message": "ok",
                "code": 0,
                "result": {
                    "summary": {
                        "related_ratio": 0.0,
                        "total_count": 0,
                        "related_count": 0
                    },
                    "distribution": []
                }
            }
        
        # 12. 构建分布明细数据
        distribution = []
        option_labels = {
            'c1': '很相关',
            'c2': '相关', 
            'c3': '基本相关',
            'c4': '不相关',
            'c5': '很不相关'
        }
        
        for key, label in option_labels.items():
            count = counts[key]
            ratio = round(count / total_count, 4) if total_count > 0 else 0.0
            distribution.append({
                "option": label,
                "count": count,
                "ratio": ratio,
                "percentage": f"{ratio * 100:.2f}%"
            })
        
        # 13. 计算前三项（相关度）汇总
        related_count = counts['c1'] + counts['c2'] + counts['c3']
        related_ratio = round(related_count / total_count, 4) if total_count > 0 else 0.0
        
        logger.info(f"相关度统计: 相关={related_count}, 总计={total_count}, 比例={related_ratio}")
        logger.info(f"分布明细: {[(d['option'], d['percentage']) for d in distribution]}")

        logger.info(f"指标 '境内升学专业相关度分布' 计算成功")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": {
                "summary": {
                    "related_ratio": related_ratio,
                    "total_count": total_count,
                    "related_count": related_count,
                    "description": f"升学专业与原专业相关度为 {related_ratio * 100:.2f}%"
                },
                "distribution": distribution
            }
        }

    except Exception as e:
        logger.error(f"计算指标 '境内升学专业相关度分布' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 境内升学专业相关度分布",
            "code": 500,
            "error": str(e)
        }